package com.wy.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wy.domain.MemberForm;
import com.wy.tool.JDBConnection;

public class MemberDao {
	private Connection connection = null;
	private PreparedStatement ps = null;
	JDBConnection jdbc = null;

	public MemberDao() {
		jdbc = new JDBConnection();
		connection = jdbc.connection;
	}

	public boolean updatePassword(String password, Integer id) {
		try {
			ps = connection
					.prepareStatement("update tb_member set password=? where id=?");
			ps.setString(1, password);
			ps.setInt(2, id.intValue());
			ps.executeUpdate();
			ps.close();
			return true;
		} catch (SQLException ex) {
			return false;
		}
	}

	// fetch password
	public MemberForm selectFind(String name, String result) {
		MemberForm member = null;
		try {
			ps = connection
					.prepareStatement("select * from tb_member where name = ? and result = ?");
			ps.setString(1, name);
			ps.setString(2, result);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				member = new MemberForm();
				member.setId(Integer.valueOf(rs.getString(1)));
				member.setName(rs.getString(2));
				member.setPassword(rs.getString(3));
				member.setReallyName(rs.getString(4));
				member.setAge(Integer.valueOf(rs.getString(5)));
				member.setProfession(rs.getString(6));
				member.setEmail(rs.getString(7));
				member.setQuestion(rs.getString(8));
				member.setResult(rs.getString(9));
			}
		} catch (SQLException ex) {

		}
		return member;
	}

	// use member name as query condition or query all members information
	public MemberForm selectMemberForm(String name) {
		MemberForm member = null;
		try {
			ps = connection
					.prepareStatement("select * from tb_member where name = ?");
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				member = new MemberForm();
				member.setId(Integer.valueOf(rs.getString(1)));
				member.setName(rs.getString(2));
				member.setPassword(rs.getString(3));
				member.setReallyName(rs.getString(4));
				member.setAge(Integer.valueOf(rs.getString(5)));
				member.setProfession(rs.getString(6));
				member.setEmail(rs.getString(7));
				member.setQuestion(rs.getString(8));
				member.setResult(rs.getString(9));
			}
		} catch (SQLException ex) {

		}
		return member;
	}

	public boolean deleteMember(Integer id) {
		try {
			ps = connection
					.prepareStatement("delete from tb_member where id = ?");
			ps.setString(1, id.toString());
			ps.executeUpdate();
			ps.close();
			return true;
		} catch (SQLException ex) {
			return false;
		}
	}

	public void insertMember(MemberForm form) {
		try {
			ps = connection
					.prepareStatement("insert into tb_member values(?,?,?,?,?,?,?,?)");
			ps.setString(1, form.getName());
			ps.setString(2, form.getPassword());
			ps.setString(3, form.getReallyName());
			ps.setString(4, form.getAge().toString());
			ps.setString(5, form.getProfession());
			ps.setString(6, form.getEmail());
			ps.setString(7, form.getQuestion());
			ps.setString(8, form.getResult());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException ex) {

		}
	}

	// update information us serial number as condition
	public void updateMember(MemberForm form) {
		try {
			ps = connection
					.prepareStatement("update tb_member set name = ?,password = ?,reallyName = ?,age = ?,profession = ?,email = ?,question = ?,reult = ? where id = ?");
			ps.setString(1, form.getName());
			ps.setString(2, form.getPassword());
			ps.setString(3, form.getReallyName());
			ps.setString(4, form.getAge().toString());
			ps.setString(5, form.getProfession());
			ps.setString(6, form.getEmail());
			ps.setString(7, form.getQuestion());
			ps.setString(8, form.getResult());
			ps.setString(9, form.getId().toString());
			ps.executeQuery();
			ps.close();
		} catch (SQLException ex) {
		}
	}

	public List selectMember() {
		List list = new ArrayList();
		MemberForm member = null;
		try {
			ps = connection
					.prepareStatement("select * from tb_member order by id DESC");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				member = new MemberForm();
				member.setId(Integer.valueOf(rs.getString(1)));
				member.setName(rs.getString(2));
				member.setPassword(rs.getString(3));
				member.setReallyName(rs.getString(4));
				member.setAge(Integer.valueOf(rs.getString(5)));
				member.setProfession(rs.getString(6));
				member.setEmail(rs.getString(7));
				member.setQuestion(rs.getString(8));
				member.setResult(rs.getString(9));
				list.add(member);
			}
		} catch (SQLException ex) {

		}
		return list;
	}

	// use database serial number as query condition
	public MemberForm selectOneMember(Integer id) {
		MemberForm member = null;
		try {
			ps = connection
					.prepareStatement("select * from tb_member where id = ?");
			ps.setInt(1, id.intValue());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				member.setId(Integer.valueOf(rs.getString(1)));
				member.setName(rs.getString(2));
				member.setPassword(rs.getString(3));
				member.setReallyName(rs.getString(4));
				member.setAge(Integer.valueOf(rs.getString(5)));
				member.setProfession(rs.getString(6));
				member.setEmail(rs.getString(7));
				member.setQuestion(rs.getString(8));
				member.setResult(rs.getString(9));
			}
		} catch (SQLException ex) {

		}
		return member;
	}
}
